1 <?php
2 // check this file's MD5 to make sure it wasn't called before
3 $prevMD5=@implode('', @file(dirname(__FILE__).'/setup.md5'));
4 $thisMD5=md5(@implode('', @file("./updateDB.php")));
5 if($thisMD5==$prevMD5){
6 $setupAlreadyRun=true;
7 }else{
8 // set up tables
9 if(!isset($silent)){
10 $silent=true;
11 }
12
13 // set up tables
14 setupTable('patients', "create table if not exists `patients` ( `id` INT unsigned not null auto_increment , primary key (`id`), `last_name` VARCHAR(40) not null , `first_name` VARCHAR(40) not null , `gender` VARCHAR(10) not null default 'Unknown' , `sexual_orientation` TEXT not null , `birth_date` DATE , `age` INT , `image` VARCHAR(40) , `address` TEXT , `city` VARCHAR(40) , `state` VARCHAR(15) , `zip` CHAR(8) , `home_phone` VARCHAR(40) , `work_phone` VARCHAR(40) , `mobile` VARCHAR(40) , `tobacco_usage` VARCHAR(40) not null default 'Unknown' , `alcohol_intake` VARCHAR(40) not null default 'Unknown' , `history` VARCHAR(100) not null default 'Unknown' , `surgical_history` TEXT , `obstetric_history` TEXT , `genetic_diseases` TEXT , `contact_person` VARCHAR(100) , `other_details` TEXT , `comments` TEXT , `filed` DATETIME , `last_modified` DATETIME ) CHARSET latin1", $silent);
15 setupTable('disease_symptoms', "create table if not exists `disease_symptoms` ( `id` INT unsigned not null auto_increment , primary key (`id`), `disease` VARCHAR(200) , `symptoms` TEXT , `reference` TEXT ) CHARSET latin1", $silent);
16 setupTable('medical_records', "create table if not exists `medical_records` ( `id` INT unsigned not null auto_increment , primary key (`id`), `patient` INT unsigned , `image_1` VARCHAR(40) , `image_2` VARCHAR(40) , `image_3` VARCHAR(40) , `image_4` VARCHAR(40) , `image_5` VARCHAR(40) , `document_1` VARCHAR(40) , `document_2` VARCHAR(40) , `document_3` VARCHAR(40) , `document_4` VARCHAR(40) , `document_5` VARCHAR(40) , `description` TEXT ) CHARSET latin1", $silent, array( "ALTER TABLE `medical_records` CHANGE `document` `image_1` VARCHAR(40) ","ALTER TABLE `medical_records` CHANGE `image_1` `image_1` VARCHAR(40) ","ALTER TABLE `medical_records` ADD `image_1_1` VARCHAR(40) ","ALTER TABLE `medical_records` ADD `image_1_2` VARCHAR(40) ","ALTER TABLE `medical_records` ADD `image_1_3` VARCHAR(40) ","ALTER TABLE `medical_records` ADD `image_1_4` VARCHAR(40) ","ALTER TABLE `medical_records` CHANGE `image_1_1` `image_5` VARCHAR(40) ","ALTER TABLE `medical_records` CHANGE `image_1_2` `image_2` VARCHAR(40) ","ALTER TABLE `medical_records` CHANGE `image_1_3` `image_3` VARCHAR(40) ","ALTER TABLE `medical_records` CHANGE `image_1_4` `image_4` VARCHAR(40) ","ALTER TABLE medical_records ADD `field9` VARCHAR(40)","ALTER TABLE `medical_records` CHANGE `field9` `document_1` VARCHAR(40) ","ALTER TABLE `medical_records` ADD `document_1_1` VARCHAR(40) ","ALTER TABLE `medical_records` ADD `document_1_1_1` VARCHAR(40) ","ALTER TABLE `medical_records` DROP `document_1_1_1`","ALTER TABLE `medical_records` ADD `document_1_1_1` VARCHAR(40) ","ALTER TABLE `medical_records` DROP `document_1_1_1`","ALTER TABLE `medical_records` ADD `document_1_2` VARCHAR(40) ","ALTER TABLE `medical_records` ADD `document_1_3` VARCHAR(40) ","ALTER TABLE `medical_records` ADD `document_1_4` VARCHAR(40) ","ALTER TABLE `medical_records` CHANGE `document_1` `document_5` VARCHAR(40) ","ALTER TABLE `medical_records` CHANGE `document_1_1` `document_1` VARCHAR(40) ","ALTER TABLE `medical_records` CHANGE `document_1_2` `document_2` VARCHAR(40) ","ALTER TABLE `medical_records` CHANGE `document_1_3` `document_3` VARCHAR(40) ","ALTER TABLE `medical_records` CHANGE `document_1_4` `document_4` VARCHAR(40) "));
17 setupIndexes('medical_records', array('patient'));
18 setupTable('events', "create table if not exists `events` ( `id` INT unsigned not null auto_increment , primary key (`id`), `title` VARCHAR(40) , `date` DATE , `status` VARCHAR(40) not null , `name_patient` INT unsigned , `time` TIME default '12:00' , `prescription` VARCHAR(40) , `diagnosis` VARCHAR(40) , `comments` TEXT ) CHARSET latin1", $silent, array( "ALTER TABLE `events` ADD `document_1` VARCHAR(40) ","ALTER TABLE `events` DROP `document_1`"));
19 setupIndexes('events', array('name_patient'));
20
21
22 // save MD5
23 if($fp=@fopen(dirname(__FILE__).'/setup.md5', 'w')){
24 fwrite($fp, $thisMD5);
25 fclose($fp);
26 }
27 }
28
29
30 function setupIndexes($tableName, $arrFields){
31 if(!is_array($arrFields)){
32 return false;
33 }
34
35 foreach($arrFields as $fieldName){
36 if(!$res=@db_query("SHOW COLUMNS FROM `$tableName` like '$fieldName'")){
37 continue;
38 }
39 if(!$row=@db_fetch_assoc($res)){
40 continue;
41 }
42 if($row['Key']==''){
43 @db_query("ALTER TABLE `$tableName` ADD INDEX `$fieldName` (`$fieldName`)");
44 }
45 }
46 }
47
48
49 function setupTable($tableName, $createSQL='', $silent=true, $arrAlter=''){
50 global $Translation;
51 ob_start();
52
53 echo '<div style="padding: 5px; border-bottom:solid 1px silver; font-family: verdana, arial; font-size: 10px;">';
54
55 // is there a table rename query?
56 if(is_array($arrAlter)){
57 $matches=array();
58 if(preg_match("/ALTER TABLE `(.*)` RENAME `$tableName`/", $arrAlter[0], $matches)){
59 $oldTableName=$matches[1];
60 }
61 }
62
63 if($res=@db_query("select count(1) from `$tableName`")){ // table already exists
64 if($row = @db_fetch_array($res)){
65 echo str_replace("<TableName>", $tableName, str_replace("<NumRecords>", $row[0],$Translation["table exists"]));
66 if(is_array($arrAlter)){
67 echo '<br>';
68 foreach($arrAlter as $alter){
69 if($alter!=''){
70 echo "$alter ... ";
71 if(!@db_query($alter)){
72 echo '<span class="label label-danger">' . $Translation['failed'] . '</span>';
73 echo '<div class="text-danger">' . $Translation['mysql said'] . ' ' . db_error(db_link()) . '</div>';
74 }else{
75 echo '<span class="label label-success">' . $Translation['ok'] . '</span>';
76 }
77 }
78 }
79 }else{
80 echo $Translation["table uptodate"];
81 }
82 }else{
83 echo str_replace("<TableName>", $tableName, $Translation["couldnt count"]);
84 }
85 }else{ // given tableName doesn't exist
86
87 if($oldTableName!=''){ // if we have a table rename query
88 if($ro=@db_query("select count(1) from `$oldTableName`")){ // if old table exists, rename it.
89 $renameQuery=array_shift($arrAlter); // get and remove rename query
90
91 echo "$renameQuery ... ";
92 if(!@db_query($renameQuery)){
93 echo '<span class="label label-danger">' . $Translation['failed'] . '</span>';
94 echo '<div class="text-danger">' . $Translation['mysql said'] . ' ' . db_error(db_link()) . '</div>';
95 }else{
96 echo '<span class="label label-success">' . $Translation['ok'] . '</span>';
97 }
98
99 if(is_array($arrAlter)) setupTable($tableName, $createSQL, false, $arrAlter); // execute Alter queries on renamed table ...
100 }else{ // if old tableName doesn't exist (nor the new one since we're here), then just create the table.
101 setupTable($tableName, $createSQL, false); // no Alter queries passed ...
102 }
103 }else{ // tableName doesn't exist and no rename, so just create the table
104 echo str_replace("<TableName>", $tableName, $Translation["creating table"]);
105 if(!@db_query($createSQL)){
106 echo '<span class="label label-danger">' . $Translation['failed'] . '</span>';
107 echo '<div class="text-danger">' . $Translation['mysql said'] . db_error(db_link()) . '</div>';
108 }else{
109 echo '<span class="label label-success">' . $Translation['ok'] . '</span>';
110 }
111 }
112 }
113
114 echo "</div>";
115
116 $out=ob_get_contents();
117 ob_end_clean();
118 if(!$silent){
119 echo $out;
120 }
121 }
122 ?>
